﻿using Common.Extend;
using DB;
using Model.Base;
using Model.Extend;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Reflection;
using System.Text;

namespace IDAL
{
    public abstract class BaseADO<T> : IExSelectADO<T> where T:class,new()
    {
        protected DBHelpers db = DBFactorys.GetDBHelper();
        #region 辅助方法
        private static Type type = typeof(T);
        protected static PropertyInfo[] pros = type.GetProperties();
        protected static string TableName = ModelAttribute.GetTableName<T>();
        /// <summary>
        /// 从实体中获取指定字段及其值
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="listUpdateField"></param>
        /// <param name="m"></param>
        /// <returns></returns>
        public virtual Dictionary<string, object> GetKeyValue(IList<string> listUpdateField, T m)
        {
            Dictionary<string, object> listKV = new Dictionary<string, object>();
            if (m == null)
                return listKV;

            foreach (string str in listUpdateField)
            {
                foreach (PropertyInfo pro in pros)
                {
                    if (str == pro.Name)
                    {
                        listKV.Add(str, pro.GetValue(m, null));
                        continue;
                    }
                }
            }

            return listKV;
        }
        #endregion

        #region 查询
        /// <summary>
        /// 根据条件获取一个包含全部查询结果的DataTable
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <returns></returns>
        public virtual DataTable GetTable(T m)
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendFormat("SELECT {0} FROM [{1}] ", string.Join(",", pros.Select(p => string.Format("[{0}]", p.Name))), TableName);

            //拼接查询条件字符串
            if (m != null)
            {
                string sqlWhere = string.Join(" AND ", pros.Where(p =>
                {
                    Type t = p.PropertyType;
                    object value = p.GetValue(m, null);
                    if (t.IsValueType)
                    {
                        return value != null && !Activator.CreateInstance(t).Equals(value) && t != typeof(DateTime);
                    }
                    else
                    {
                        return t == typeof(String) && value != null && value.ToString() != "";
                    }
                }).Select(p => string.Format("[{0}]=@{0}", p.Name)));
                if (string.IsNullOrEmpty(sqlWhere) == false)
                {
                    sql.Append(" WHERE " + sqlWhere);
                }
            }

            DbParameter[] ps = ADOHelper.SetParmValues(m, false);
            return db.GetTableText(sql.ToString(), ps)[0];
        }
        /// <summary>
        /// 根据条件获取一个包含全部查询结果的DataTable（参数集合名称不能与实体属性名称相同）
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="strWhere">sql查询条件字符串</param>
        /// <param name="paramFields">参数集合</param>
        /// <returns></returns>
        public virtual DataTable GetTable(string strWhere, Dictionary<string, object> paramFields)
        {
            if (string.IsNullOrWhiteSpace(strWhere) == false)
            {
                if (strWhere.Trim().Substring(0, 3).ToLower() != "and")
                    strWhere = " AND " + strWhere;
            }
            StringBuilder sql = new StringBuilder();
            sql.AppendFormat("SELECT {0} FROM [{1}] WHERE 1=1 {2}", string.Join(",", pros.Select(p => string.Format("[{0}]", p.Name))), TableName, strWhere);


            DbParameter[] paramPS = ADOHelper.ToArrSqlParam(paramFields);
            return db.GetTableText(sql.ToString(), paramPS)[0];
        }
        /// <summary>
        /// 根据条件获取一个包含全部查询结果的List数集
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="m"></param>
        /// <returns></returns>
        public virtual List<T> GetList(T m)
        {
            DataTable dt = GetTable(m);
            return dt.ExToList<T>();
        }
        /// <summary>
        /// 根据条件获取一个包含全部查询结果的List数集（参数集合名称不能与实体属性名称相同）
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="strWhere">sql查询条件字符串</param>
        /// <param name="paramFields">参数集合</param>
        /// <returns></returns>
        public virtual List<T> GetList(string strWhere, Dictionary<string, object> paramFields)
        {
            DataTable dt = GetTable(strWhere, paramFields);
            return dt.ExToList<T>();
        }
        /// <summary>
        /// 根据条件分页获取一个包含查询结果的DataTable
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="p"></param>
        /// <param name="t"></param>
        /// <returns></returns>
        public virtual DataTable GetListByPage(Common.DB.Paging p, T m)
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendFormat("SELECT {0} FROM [{1}] ", string.Join(",", pros.Select(s => string.Format("[{0}]", s.Name))), TableName);


            if (m != null)
            {
                string sqlWhere = string.Join(" AND ", pros.Where(s =>
                {
                    Type t = s.PropertyType;
                    object value = s.GetValue(m, null);
                    if (t.IsValueType)
                    {
                        return value != null && !Activator.CreateInstance(t).Equals(value) && t != typeof(DateTime);
                    }
                    else
                    {
                        return t == typeof(String) && value != null && value.ToString() != "";
                    }
                }).Select(s => string.Format("[{0}]=@{0}", s.Name)));
                if (string.IsNullOrEmpty(sqlWhere) == false)
                {
                    sql.Append(" WHERE " + sqlWhere);
                }
            }

            StringBuilder sqlCount = new StringBuilder();
            sqlCount.AppendFormat("SELECT COUNT(1) FROM ({0}) AS TotalCount", sql);

            StringBuilder sqlPage = new StringBuilder();
            sqlPage.AppendFormat("SELECT TOP {1} * FROM(SELECT ROW_NUMBER() OVER (ORDER BY {2} {3}) AS RowNO,* FROM ({0}) AS TotalTable) AS PageTable WHERE RowNO>{4} ", sql, p.PageSize, p.SortFields, p.SortType, (p.CurrentPageIndex - 1) * p.PageSize);

            DbParameter[] ps = ADOHelper.SetParmValues(m, false);
            DataTableCollection dtC = db.GetTableText(sqlCount.ToString() + " " + sqlPage.ToString(), ps);
            if (dtC.Count == 2)
            {
                if (dtC[0].Rows.Count > 0)
                {
                    object objTotal = dtC[0].Rows[0][0];
                    if (objTotal != null && !(objTotal is DBNull))
                        p.TotalCount = Convert.ToInt32(objTotal);
                }
            }
            return dtC[1];
        }
        /// <summary>
        /// 根据条件分页获取一个包含查询结果的DataTable（参数集合名称不能与实体属性名称相同）
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="p">分页查询参数类</param>
        /// <param name="strWhere">sql查询条件字符串</param>
        /// <param name="paramFields">参数集合</param>
        /// <returns></returns>
        public virtual DataTable GetListByPage(Common.DB.Paging p, string strWhere, Dictionary<string, object> paramFields)
        {
            if (string.IsNullOrWhiteSpace(strWhere) == false)
            {
                if (strWhere.Trim().Substring(0, 3).ToLower() != "and")
                    strWhere = " AND " + strWhere;
            }
            StringBuilder sql = new StringBuilder();
            sql.AppendFormat("SELECT {0} FROM [{1}] WHERE 1=1 {2}", string.Join(",", pros.Select(s => string.Format("[{0}]", s.Name))), TableName, strWhere);


            StringBuilder sqlCount = new StringBuilder();
            sqlCount.AppendFormat("SELECT COUNT(1) FROM ({0}) AS TotalCount", sql);

            StringBuilder sqlPage = new StringBuilder();
            sqlPage.AppendFormat("SELECT TOP {1} * FROM(SELECT ROW_NUMBER() OVER (ORDER BY {2} {3}) AS RowNO,* FROM ({0}) AS TotalTable) AS PageTable WHERE RowNO>{4} ", sql, p.PageSize, p.SortFields, p.SortType, (p.CurrentPageIndex - 1) * p.PageSize);

            DbParameter[] paramPS = ADOHelper.ToArrSqlParam(paramFields);
            DataTableCollection dtC = db.GetTableText(sqlCount.ToString() + " " + sqlPage.ToString(), paramPS);
            if (dtC.Count == 2)
            {
                if (dtC[0].Rows.Count > 0)
                {
                    object objTotal = dtC[0].Rows[0][0];
                    if (objTotal != null && !(objTotal is DBNull))
                        p.TotalCount = Convert.ToInt32(objTotal);
                }
            }
            return dtC[1];
        }
        #endregion
    }
}
